﻿CREATE PROCEDURE UpdateSystemInfosForUser
	@iid uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @lastsubmit datetime = (
		select top 1 [Submission]
		from [UserSystemInfos]
		where [InstanceId]=@iid
		order by [Submission] desc
		);

	merge [UserSystemInfosLatest] t
	using (select * from [UserSystemInfos] where [InstanceId]=@iid and [Submission]=@lastsubmit) s
	on t.[InstanceId]=s.[InstanceId]
	and t.[InstanceId]=@iid
	when matched then
		update set [Submission]=s.[Submission], [SystemManufacturer]=s.[SystemManufacturer],
			[SystemModel]=s.[SystemModel], [OsVersion]=s.[OsVersion],
			[WhsVersion]=s.[WhsVersion], [Locale]=s.[Locale],
			[TimeZone]=s.[TimeZone], [TotalRam]=s.[TotalRam],
			[WhsDomain]=s.[WhsDomain]
	when not matched by source and t.[InstanceId]=@iid then
		delete
	when not matched by target then
		insert ([InstanceId],[Submission],[SystemManufacturer],[SystemModel],[OsVersion],[WhsVersion],[Locale],[TimeZone],[TotalRam],[WhsDomain])
		values (s.[InstanceId],s.[Submission],s.[SystemManufacturer],s.[SystemModel],s.[OsVersion],s.[WhsVersion],s.[Locale],s.[TimeZone],s.[TotalRam],s.[WhsDomain])
	;
END
GO
